﻿Imports Microsoft.VisualBasic

Public Class permissions

    'membros ou campos
    Public id_permissions As Integer
    Public permissions As String
    Public description As String

    'nascimento da variavel, ou seja o construtor
    Public Sub New()

        id_permissions = 0
        permissions = ""
        description = ""
    End Sub



    Public Property description_() As String
        Get
            Return description
        End Get
        Set(ByVal value As String)
            description = value
        End Set
    End Property

    Function insert() As String   ' se inseriu bem ou não 




        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim string_sql As String = "INSERT INTO permissions(permission,description) VALUES(' " + permissions + " ',' " + description + "');"
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = string_sql ' define o sql que o comando vai executar'
        SqlCommand1.ExecuteNonQuery()  ' executa do comando na base de dados '
        SqlConnection1.Close()   ' fecha a ligação à base de dados '

        Return string_sql


    End Function


    Function update() As String   ' se inseriu bem ou não 




        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()

        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim string_sql As String = "update permissions set permission = '" + permissions + "' , description =  '" + description + "'  where id_permissions= " + id_permissions.ToString()
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = string_sql ' define o sql que o comando vai executar'
        SqlCommand1.ExecuteNonQuery()  ' executa do comando na base de dados '
        SqlConnection1.Close()   ' fecha a ligação à base de dados '


        Return string_sql

    End Function


    Function first() As String

        Dim sql_first As String = " select top 1 id_permissions,permission, description from permissions ORDER BY id_permissions ASC "
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()
        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = sql_first



        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader


        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_permissions = SqlDataReader1.GetInt32(0)
            permissions = SqlDataReader1.GetString(1)
            description = SqlDataReader1.GetString(2)

        End If

        SqlDataReader1.Close()
        SqlConnection1.Close()

        Return sql_first


    End Function


    Function next_permissions() As String

        Dim sql_next As String = " select top 1 id_permissions,permission, description from permissions where id_permissions > " + id_permissions.ToString + "  ORDER BY id_permissions ASC "
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()
        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = sql_next



        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader


        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_permissions = SqlDataReader1.GetInt32(0)
            permissions = SqlDataReader1.GetString(1)
            description = SqlDataReader1.GetString(2)

        End If

        SqlDataReader1.Close()
        SqlConnection1.Close()

        Return sql_next


    End Function


    Function previous_permissions() As String

        Dim sql_previous As String = " select top 1 id_permissions,permission, description from permissions where id_permissions < " + id_permissions.ToString() + "  ORDER BY id_permissions ASC "
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()
        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = sql_previous



        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader


        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_permissions = SqlDataReader1.GetInt32(0)
            permissions = SqlDataReader1.GetString(1)
            description = SqlDataReader1.GetString(2)

        End If

        SqlDataReader1.Close()
        SqlConnection1.Close()

        Return sql_previous


    End Function



    Function last_permissions() As String

        Dim sql_last As String = " select top 1 id_permissions,permission, description from permissions ORDER BY id_permissions DESC "
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()
        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = sql_last



        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader


        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_permissions = SqlDataReader1.GetInt32(0)
            permissions = SqlDataReader1.GetString(1)
            description = SqlDataReader1.GetString(2)

        End If

        SqlDataReader1.Close()
        SqlConnection1.Close()

        Return sql_last


    End Function



    'função para elemina os valores da base de dados
    Function delet() As String 'se inseriu bem ou não

        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim sqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        sqlConnection1.ConnectionString = connection_string
        sqlConnection1.Open()

        Dim sqlcomand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim String_delet As String = "delete from permissions where id_permissions = " + id_permissions.ToString()

        'associa o comando a executar com a conexão
        sqlcomand1.Connection = sqlConnection1

        'definir o sql que o comando vai executar
        sqlcomand1.CommandText = String_delet

        'Executa do comando na base de dados base de dados
        sqlcomand1.ExecuteNonQuery()

        'Fecha a ligação á base de dados
        sqlConnection1.Close()

        Return String_delet
    End Function



    Function select_by_id() As String 'se inseriu bem ou não

        Dim sql_select_by_id As String = " select id_permissions,permission, description from permissions where id_permissions = " + id_permissions.ToString()
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        SqlConnection1.ConnectionString = connection_string
        SqlConnection1.Open()
        Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        SqlCommand1.Connection = SqlConnection1 'associa o comando a executar com a conexão'
        SqlCommand1.CommandText = sql_select_by_id



        Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader


        SqlDataReader1 = SqlCommand1.ExecuteReader()
        If SqlDataReader1.Read() Then
            id_permissions = SqlDataReader1.GetInt32(0)
            permissions = SqlDataReader1.GetString(1)
            description = SqlDataReader1.GetString(2)

        End If

        SqlDataReader1.Close()
        SqlConnection1.Close()

        Return sql_select_by_id

    End Function


End Class
